Opening hours

Column

Description

There are 11 hub libraries grouped into 3 clusters. There are 5, 3 and 3 libraries in clusters 1, 2 and 3 respectively.

Every hub library is open for 31 hours a week. No libraries open on Sunday. All libraries are open on Monday, Friday and Saturday. 6 libraries close on Thursday, 4 on Wednesday and 1 on Tuesday.

Note: We’re waiting on data for the central library.

Hours a week

cluster library hours_open
1 Chapeltown 31
1 Firth Park 31
1 Hillsborough 31
1 Parson Cross 31
1 Stocksbridge 31
2 Ecclesall 31
2 Highfield 31
2 Woodseats 31
3 Crystal Peaks 31
3 Darnall 31
3 Manor 31

Shift summary

day shifts_cluster1 shifts_cluster2 shifts_cluster3
Monday 5 3 3
Tuesday 5 2 3
Wednesday 2 3 2
Thursday 3 1 1
Friday 5 3 3
Saturday 5 3 3

Column

Library opening

List of library opening

Staff schedules

Column

Description

The average FTE scheduled staff hours per hub library is 3.4.

Stocksbridge and Parson Cross libraries have the least FTE (Full Time Equivalent) staff scheduled with an average of 2.8, whilst Firth Park has the most with 4.9.

By library

library staff_hours fte
Ecclesall 85:36:15 2.3
home 89:50:00 2.4
Darnall 90:57:30 2.5
Stocksbridge 102:30:00 2.8
Manor 102:56:15 2.8
Parson Cross 102:56:15 2.8
Woodseats 109:36:15 3.0
Hillsborough 111:51:15 3.0
Highfield 119:26:15 3.2
Crystal Peaks 135:00:00 3.6
Chapeltown 149:37:30 4.0
Firth Park 182:36:15 4.9

Note: Hours are an average over 4 weeks.

Column

Staff hours by library & week

Activities

Column

Description

The differences in FTE scheduled hours between libraries is not completely explained by the different number of activities at different libraries.

Note: Activities data only available for 4 out of 5 libraries in cluster 1. None available for clusters 2 and 3.

By library

library activities_per_week fte
Chapeltown 6 4.0
Firth Park 6 4.9
Hillsborough 6 3.0
Stocksbridge 4 2.8

Column

Activities & scheduled staff hours by library

List of activities

---
title: "Libraries dashboard (`r format(Sys.time(), '%d/%m/%y')`)"
output: 
  flexdashboard::flex_dashboard:
    orientation: columns
    vertical_layout: fill
    source_code: embed
---

```{r setup, include=FALSE}
library(flexdashboard)
library(kableExtra); library(DT)
library(writexl)

source("LibraryFunctions.R")

# Define a theme for the plots in the dashboard 
theme_set(theme_minimal())

# Used to create Excel workbook containing output
output_list <- list()
```

```{r global, include=FALSE}
# Load data in 'global' chunk so it can be shared across the dashboard

# Libraries 
libraries <- read_xlsx(libraries_data_in_xlsx, "library") %>% 
  mutate(library_id = as.integer(library_id),
         cluster = as.integer(cluster)) %>% 
  arrange(cluster, library)

# Hub libraries
hubs <- libraries %>% 
  filter(library_type == "hub")

# Open hours 
opening <- read_xlsx(libraries_data_in_xlsx, "opening") %>% 
  mutate(library_id = as.integer(library_id),
         day = factor(day, levels = days_of_work_week),
         start = hms::as_hms(start),
         end = hms::as_hms(end),
         hours_open = hms::as_hms((end - start))) %>% 
  right_join(select(hubs, library_id, cluster),
             by = c("library_id" = "library_id")) %>% 
  relocate(cluster, .after = library) %>% 
  arrange(cluster, library)

# Staff
staff <- read_xlsx(libraries_data_in_xlsx, "staff") %>%
  mutate(post_id = as.integer(post_id),
         cluster = as.integer(cluster),
         library_id = as.integer(library_id),
         name = str_trim(str_c(first_name, " ", str_replace_na(last_name, ""))),
         contract_hours = hms(hours = contract_hours)) %>%
  select(post_id, post_number, name, first_name, last_name,
         contract_hours, library_id, location, cluster)

# Staff variables  
staff_cost <- get_var("staff_cost")
fte_hours <- get_var("fte_hours")
weeks_in_rota_period <- 4

# Schedules
schedule <- read_xlsx(libraries_data_in_xlsx, "schedule") %>% 
  mutate(post_id = as.integer(post_id),
         library_id = as.integer(library_id),
         week_no = as.integer(week_no),
         day = factor(day, levels = days_of_work_week),
         start = hms::as_hms(start),
         end = hms::as_hms(end),
         lunch = hms::as_hms(lunch),
         daily_hours = hms::as_hms((end - start - lunch))) %>% 
  filter(schedule_type == "current") %>% 
  right_join(libraries, by = c("library_id" = "library_id")) %>% 
  left_join(select(staff, post_id, name), 
            by = c("post_id" = "post_id")) %>% 
  filter(name != "VACANT") %>%
  select(cluster, library_id, library, week_no, 
         day, start, end, lunch, daily_hours, name, post_id) %>% 
  arrange(cluster, library, week_no, day, name)

# Activity type
activity_type <- read_xlsx(libraries_data_in_xlsx, "activity_type") %>% 
  mutate(activity_type_id = as.integer(activity_type_id))

# Activities
activities <- read_xlsx(libraries_data_in_xlsx, "activities") %>% 
  mutate(activity_type_id = as.integer(activity_type_id),
         opening_id = as.integer(opening_id)) %>% 
  left_join(activity_type, by = c("activity_type_id" = "activity_type_id")) %>% 
  left_join(opening, by = c("opening_id" = "opening_id")) %>% 
  relocate(starts_with("activity"), .after = last_col())
```

# Opening hours

## Column {.tabset .tabset-fade data-width="300"}

### Description

```{r library-desc}
# Number of hub libraries
n_hubs <- nrow(hubs)

# Number of hub libraries in each cluster
n_hubs_cluster1 <- nrow(filter(hubs, cluster == 1))
n_hubs_cluster2 <- nrow(filter(hubs, cluster == 2))
n_hubs_cluster3 <- nrow(filter(hubs, cluster == 3))

# Number of hours open a week for each library
opening_summary <- opening %>%
  count(library_id, library, cluster, 
        wt = hours_open/dhours(1), name = "hours_open") %>% 
  arrange(cluster, library)

#Average hours open a week
n_hours_open <- as.integer(tally(opening_summary, hours_open)/n_hubs)
```

There are `r n_hubs` hub libraries grouped into `r n_clusters` clusters. There are `r n_hubs_cluster1`, `r n_hubs_cluster2` and `r n_hubs_cluster3` libraries in clusters 1, 2 and 3 respectively.

Every hub library is open for `r n_hours_open` hours a week. No libraries open on Sunday. All libraries are open on Monday, Friday and Saturday. 6 libraries close on Thursday, 4 on Wednesday and 1 on Tuesday.

*Note: We're waiting on data for the central library.*

### Hours a week

```{r library-total-hours}
# Present in a nice table
kable(select(opening_summary, cluster, library, hours_open)) %>% 
  kable_styling("striped", position = "left", full_width = FALSE)
```

### Shift summary

```{r shifts-summary}
shifts_cluster1 <- opening %>%
  filter(cluster == 1) %>% 
  count(day, name = "shifts_cluster1")

shifts_cluster2 <- opening %>%
  filter(cluster == 2) %>% 
  count(day, name = "shifts_cluster2")

shifts_cluster3 <- opening %>%
  filter(cluster == 3) %>% 
  count(day, name = "shifts_cluster3")

shifts_summary <- shifts_cluster1 %>% 
  left_join(shifts_cluster2, by = "day") %>% 
  left_join(shifts_cluster3, by = "day")

# Present in a nice table
kable(shifts_summary) %>% 
  kable_styling("striped", position = "left", full_width = FALSE)
```

## Column {.tabset .tabset-fade}

### Library opening

```{r plot-opening}
opening %>%
  arrange(library) %>%
  mutate(type = "opening hours") %>%
  rename(activity = library) %>%
  library_hours_plot("")
```

### List of library opening

```{r list-opening, warning = FALSE}
# Don't need to show the ID fields in the dashboard
opening_list <- opening %>%
  select(-ends_with("id"))

# Present in a nice table
datatable(opening_list, rownames = FALSE, filter = "bottom",
          options = list(scrollX = TRUE))
```

# Staff schedules

## Column {.tabset .tabset-fade data-width="300"}

### Description

```{r schedules-desc, include=FALSE, warning = FALSE}
# Scheduled staff hours by library
hours_by_library <- schedule %>% 
  group_by(library_id, library) %>% 
  summarise(staff_hours = hms::as_hms(sum(daily_hours)/weeks_in_rota_period)) %>% 
  ungroup() %>% 
  mutate(fte = round(staff_hours/dhours(fte_hours), 1)) %>% 
  filter(!is.na(staff_hours)) %>% 
  arrange(staff_hours)

# Number of FTE hub library staff scheduled
n_hub_scheduled_fte <- round((sum(hours_by_library$staff_hours)/dhours(1))/fte_hours, 1)

# Average number of FTE staff scheduled per hub library
n_ave_hub_scheduled_fte <-  round(n_hub_scheduled_fte/n_hubs, 1)
```

The average FTE scheduled staff hours per hub library is `r n_ave_hub_scheduled_fte`. 

Stocksbridge and Parson Cross libraries have the least FTE (Full Time Equivalent) staff scheduled with an average of 2.8, whilst Firth Park has the most with 4.9.

### By library

```{r hours-by-library, warning = FALSE}
# Present in a nice table
select(hours_by_library, library, staff_hours, fte) %>% 
  kable() %>% 
  kable_styling("striped", position = "left", full_width = FALSE)
```

*Note: Hours are an average over 4 weeks.*

## Column {.tabset .tabset-fade}

### Staff hours by library & week

```{r plot-hours-by-library-week, warning = FALSE}
# Scheduled staff hours by library & week
hours_by_library_week <- schedule %>% 
  rename(week = week_no) %>% 
  group_by(library, week) %>% 
  summarise(staff_hours = hms::as_hms(sum(daily_hours))) %>% 
  mutate(fte = round(staff_hours/dhours(fte_hours), 1),
         week = factor(week)) %>% 
  filter(!is.na(staff_hours)) %>% 
  arrange(library, week)

# Plot of staff hours by library and week
ggplot(data=hours_by_library_week, 
       aes(x=library, y=fte, fill=week)) +
    geom_bar(stat="identity", position=position_dodge()) +
    theme(axis.text.x = element_text(angle = 315, hjust=0.1),
          plot.margin = unit(c(5.5,12,5.5,5.5), "pt"),
          legend.position = c(0.8, 0.8),
          legend.key.size = unit(0.4, 'cm'))
```

# Activities

## Column {.tabset .tabset-fade data-width="300"}

### Description

The differences in FTE scheduled hours between libraries is not completely explained by the different number of activities at different libraries.

*Note: Activities data only available for 4 out of 5 libraries in cluster 1. None available for clusters 2 and 3.*

### By library

```{r activities-by-library, warning = FALSE}
# Number of activities per library (per week)  
activities_by_library <- activities %>% 
  count(library_id, library, name = "activities_per_week") %>% 
  mutate(activities_per_week = ifelse(is.na(library), 0, activities_per_week)) %>% 
  select(-library) %>% 
  left_join(libraries, by = c("library_id" = "library_id")) %>% 
  arrange(desc(activities_per_week), library)

# Add average scheduled FTE column
activities_by_library %<>% 
  left_join(select(hours_by_library, -library), 
            by = c("library_id" = "library_id"))

# Present in a nice table
select(activities_by_library, library, activities_per_week, fte) %>% 
  kable() %>% 
  kable_styling("striped", position = "left", full_width = FALSE)
```

## Column {.tabset .tabset-fade}

### Activities & scheduled staff hours by library

```{r plot-activities-by-library, warning = FALSE}
# Define the two colours we're using to help compare activities & FTE hours
activities_colour = "blue4"
fte_colour = "olivedrab4"

# Order the libraries how we want them to be plotted
activities_by_library %<>%
  arrange(-activities_per_week, -fte) %>%
  mutate(library = factor(library, levels=library)) 

# Plot of activities and staff hours by library
ggplot(data=activities_by_library,
       aes(x=library)) +
  geom_point(aes(y=activities_per_week), colour=activities_colour, size=3) +
  geom_point(aes(y=fte), colour=fte_colour, size=3) +
  scale_x_discrete(name = "library") +
  scale_y_continuous(name = "activities per week",
                     sec.axis = sec_axis(trans=~.*1, 
                                         name="FTE scheduled hours")) +
  expand_limits(y = 0) +
  theme(axis.title.y = element_text(colour = activities_colour),
        axis.line.y = element_line(color = activities_colour),
        axis.ticks.y = element_line(color = activities_colour),
        axis.title.y.right = element_text(colour = fte_colour),
        axis.line.y.right = element_line(color = fte_colour),
        axis.ticks.y.right = element_line(color = fte_colour),
        axis.text.x = element_text(angle = 315, hjust=0.1),
        plot.margin = unit(c(5.5,12,5.5,5.5), "pt"))
```

### List of activities

```{r activities-list, warning = FALSE}
# Don't need to include ID fields in the dashboard
activities_list <- activities %>% 
  select(-ends_with("_id"))

# Present in a nice table
datatable(activities_list, rownames = FALSE, filter = "bottom",
          options = list(scrollX = TRUE))
```